/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.CompanyInfo;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import com.wttech.dzzf.process.model.ObuTemp;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.util.List;

/**
 * 生成OBU黑名单信息
 * @author MaShibo
 * @date 2017/12/22.
 */
@Repository
public class GenerateBlObuInfosDao {

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	public int queryObuTempNum(int state){
		final String sql = "SELECT COUNT(1) FROM BS_OBUTEMP A WHERE A.STATE=? ";
		return testJdbcTemplate.queryForObject(sql, Integer.class, state);
	}

	public List<ObuTemp> queryObuTempByRow(int startRow, int endRow){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM FROM BS_OBUTEMP A WHERE A.STATE = 0)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ? ";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(ObuTemp.class), startRow, endRow);
	}

	public OBU queryDzzfObuById(String obuId){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               J.CHANNELID,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.CONTRACTNO ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_OBU A\n" +
						"          LEFT JOIN JG_ORGANIZATION J\n" +
						"            ON A.CLIENTNO = J.CODE\n" +
						"         WHERE A.CONTRACTNO = ? AND A.OPTON < TO_DATE('20171215','YYYYMMDD'))\n" +
						" WHERE RM = 1";
		List<OBU> obus = (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), obuId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	public int updateObuTempState(String obuId, Integer state){
		final String sql = "UPDATE BS_OBUTEMP A SET A.STATE=? WHERE A.OBUID=? ";
		return testJdbcTemplate.update(sql, state, obuId);
	}

	@SuppressWarnings("unchecked")
	public CardInfo queryCardInfoByVeh(String vehPlate, Integer vehColor){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               F.CERTIFITYPE AS USERIDTYPE,\n" +
						"               F.CERTIFINO AS USERIDNUM,\n" +
						"               F.OWNERTYPE AS USERTYPE1,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.VEHICLEPLATE, A.VEHICLECOLOR ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_CARDINFO A\n" +
						"          JOIN AC_ACCOUNT T\n" +
						"            ON A.ACCOUNTID = T.ACCOUNTID\n" +
						"          JOIN AC_FUNDACCOUNT F\n" +
						"            ON F.FUNDACCOUNTID = T.FUNDACCOUNTID\n" +
						"         WHERE A.VEHICLEPLATE = ?\n" +
						"           AND A.VEHICLECOLOR = ?\n" +
						"           AND A.OPTON < TO_DATE('20171214 19:00:00', 'yyyyMMdd hh24:mi:ss'))\n" +
						" WHERE RM = 1";
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), vehPlate, vehColor);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}

	public int updateObuTempUserId(String obuId, String userId){
		final String sql = "UPDATE BS_OBUTEMP A SET A.USERID = ? WHERE A.OBUID=? ";
		return testJdbcTemplate.update(sql, userId, obuId);
	}

	public CompanyInfo queryCompanyInfoByCert(String certifiNo, Integer certifiType, String topClass){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.USERIDTYPE, A.USERIDNUM ORDER BY A.REGISTEREDTIME DESC) AS RM\n" +
						"          FROM KF_COMPANYINFO A\n" +
						"          JOIN JG_ORGANIZATION J\n" +
						"            ON J.CHANNELID = A.CHANNELID\n" +
						"         WHERE A.USERIDTYPE = ?\n" +
						"           AND A.USERIDNUM = ?\n" +
						"           AND J.CODE LIKE ?)\n" +
						" WHERE RM = 1";
		List<CompanyInfo> companyInfos = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CompanyInfo.class), certifiType, certifiNo, '%' + topClass);
		return CollectionUtils.isEmpty(companyInfos) ? null : companyInfos.get(0);
	}

	public List<ObuTemp> queryAllHavingUserIdObus(int startRow, int endRow){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM\n" +
						"          FROM BS_OBUTEMP A\n" +
						"         WHERE A.STATE = 0\n" +
						"            OR A.STATE = 3)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(ObuTemp.class), startRow, endRow);
	}

	public int queryObuTempNum(){
		final String sql = "SELECT COUNT(1) FROM BS_OBUTEMP  A WHERE A.STATE=0 OR A.STATE=3";
		return testJdbcTemplate.queryForObject(sql, Integer.class);
	}
}
